﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Nmo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;
using Entity;

namespace DataControls
{
   public class DETAI_Data
    {
       DataBaseAccess dataBaseAccess = new DataBaseAccess();

       public void hienthi_DeTai(DataGridView dtgDeTai,String madetai)
       {
           String excuteString = "SELECT * FROM DETAI WHERE MADT='" + madetai + "'";
           DataTable dataTable = new DataTable();
           dataTable = dataBaseAccess.getDataTable(excuteString);
           dtgDeTai.DataSource = dataTable;
       }

       public void hienthi_DeTaitheoGV(DataGridView dtgDeTai, String magiaovien)
       {
           String excuteString = "SELECT * FROM DETAI WHERE MAGVHD='" + magiaovien + "'";
           DataTable dataTable = new DataTable();
           dataTable = dataBaseAccess.getDataTable(excuteString);
           dtgDeTai.DataSource = dataTable;
       }



       public void timkiemtheo_TenDeTai(DataGridView dtgDeTai, String tendetai)
       {
           String excuteString = "SELECT * FROM DETAI WHERE  TENDT LIKE'%" + tendetai + "'";
           DataTable dataTable = new DataTable();
           dataTable = dataBaseAccess.getDataTable(excuteString);
           dtgDeTai.DataSource = dataTable;
       }

       public void timkiemtheo_TenGV(DataGridView dtgDeTai, String tengiaovien)
       {
           String excuteString = "SELECT D.* FROM DETAI D, GIAOVIEN G WHERE  G.MAGV= D.MAGVHD AND G.TENGV LIKE'%" + tengiaovien + "'";
           DataTable dataTable = new DataTable();
           dataTable = dataBaseAccess.getDataTable(excuteString);
           dtgDeTai.DataSource = dataTable;
       }




       public void detai_Combobox(ComboBox cbDeTai)
       {
           String excuteString = "SELECT * FROM DETAI";
           dataBaseAccess.addto_Combobox(cbDeTai, excuteString);
       }

       public void detaitheonamhoc_Combobox(ComboBox cbDeTai, int hocki, int namhoc)
       {
           String excuteString1 = "SELECT COUNT(*) FROM DETAI WHERE HOCKI=" + hocki + " AND NAMHOC=" + namhoc;
           int sodetai = (int)dataBaseAccess.excuteScalar(excuteString1);
           if (sodetai >= 0)
           {
               String excuteString = "SELECT * FROM DETAI WHERE HOCKI=" + hocki + " AND NAMHOC=" + namhoc;
               dataBaseAccess.addto_Combobox(cbDeTai, excuteString);
           }
           else
           {
               MessageBox.Show("Khong co de tai trong nam hoc va hoc ki ban chon");
           }
       }


       public Boolean kiemtraMaDT_NXDT(String madetai)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM NX_DETAI WHERE MADT='" + madetai + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }

       public Boolean kiemtra_MaDeTai(String madetai)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM DETAI WHERE MADT='" + madetai + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else
               return false;
       }

       public Boolean kiemtra_TenDeTai(String tendetai)
       {
           int i = 0;
           String excuteString = "SELECT COUNT(*) FROM DETAI WHERE TENDT='" + tendetai + "'";
           i = dataBaseAccess.Test(excuteString);
           if (i > 0)
               return true;
           else return false;
       }

       public void layten_LoaiDeTai(String loaidetai, ComboBox cb)
       {
           int a = 0;
           String excuteString = "SELECT LOAIDT FROM LOAIDETAI WHERE LOAIDT='" + loaidetai + "'";
           String tenloaidetai = dataBaseAccess.excuteReader(excuteString);
           for (int i = 0; i < cb.Items.Count; i++)
           {
               cb.SelectedIndex = i;
               if (loaidetai==(cb.Text))
                   a = i;
           }
           cb.SelectedIndex = a;
       }

       public void layten_GiaoVien(String magiaovien, ComboBox cb)
       {
           int a = 0;
           String excuteString = "SELECT TENGV FROM GIAOVIEN WHERE MAGV='" + magiaovien + "'";
           String tengiaovien = dataBaseAccess.excuteReader(excuteString);
           for (int i = 0; i < cb.Items.Count; i++)
           {
               cb.SelectedIndex = i;
               if (tengiaovien.Equals(((ListItem)(cb.SelectedItem)).name))
                   a = i;
           }
           cb.SelectedIndex = a;
       }

       public int tongtiet_HuongDan(String magiaovien, int hocki, int namhoc)
       {
           int tongtiet = 0;
           int sodetai = 0;
           String excuteString1 = "SELECT COUNT(*) FROM DETAI  WHERE MAGVHD='" + magiaovien +
                                                                "' AND HOCKI =" + hocki +
                                                                "AND NAMHOC =" + namhoc;
           sodetai = Convert.ToInt16(dataBaseAccess.excuteScalar(excuteString1));
           if (sodetai <= 0)
           {
              tongtiet  = 0;
           }
           else
           {
               String excuteString = "SELECT SUM(SOTIETCHUAN) FROM DETAI WHERE MAGVHD='" + magiaovien +
                                                                    "' AND HOCKI =" + hocki +
                                                                    "AND NAMHOC =" + namhoc;
               tongtiet = Convert.ToInt16(dataBaseAccess.excuteScalar(excuteString));
           }
           return tongtiet;
       }
    }
}
